﻿namespace DatabaseManage
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OracleClient;
    using System.Linq;
   

    public class Oracle : AbsDatabaseInfo
    {
        public Oracle()
        {
        }

        public Oracle(string connectionString)
        {
            base.ConnectionString = connectionString;
        }

        public override bool CheckDatabaseConnection()
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    connection.Open();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        public override string ConvertDbToCS(string dataType)
        {
            dataType = dataType.ToUpper();
            switch (dataType)
            {
                case "BFILE":
                    return "byte[]";

                case "BLOB":
                    return "byte[]";

                case "CHAR":
                    return "string";

                case "CLOB":
                    return "string";

                case "DATE":
                    return "DateTime";

                case "FLOAT":
                    return "decimal";

                case "INTEGER":
                    return "decimal";

                case "INTERVAL YEAR TO  MONTH":
                    return "Int32";

                case "INTERVAL DAY TO  SECOND":
                    return "TimeSpan";

                case "LONG":
                    return "string";

                case "LONG RAW":
                    return "byte[]";

                case "NCHAR":
                    return "string";

                case "NCLOB":
                    return "string";

                case "NUMBER":
                    return "decimal";

                case "NVARCHAR2":
                    return "string";

                case "RAW":
                    return "byte[]";

                case "ROWID":
                    return "string";

                case "TIMESTAMP":
                    return "DateTime";

                case "VARCHAR2":
                    return "string";
            }
            return "";
        }

        public override string ConvertDbToNHbm(string dataType)
        {
            dataType = dataType.ToUpper();
            switch (dataType)
            {
                case "BFILE":
                    return "Byte[]";

                case "BLOB":
                    return "Byte[]";

                case "CHAR":
                    return "String";

                case "CLOB":
                    return "String";

                case "DATE":
                    return "DateTime";

                case "FLOAT":
                    return "Decimal";

                case "INTEGER":
                    return "Decimal";

                case "INTERVAL YEAR TO  MONTH":
                    return "Int32";

                case "INTERVAL DAY TO  SECOND":
                    return "TimeSpan";

                case "LONG":
                    return "String";

                case "LONG RAW":
                    return "Byte[]";

                case "NCHAR":
                    return "String";

                case "NCLOB":
                    return "String";

                case "NUMBER":
                    return "Decimal";

                case "NVARCHAR2":
                    return "String";

                case "RAW":
                    return "Byte[]";

                case "ROWID":
                    return "String";

                case "TIMESTAMP":
                    return "DateTime";

                case "VARCHAR2":
                    return "String";
            }
            return "";
        }

        public override DataTable GetDatabaseAllTableNames()
        {
            DataTable dataTable = null;
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = "select t.table_name as TableName from user_tables t order by t.table_name";
                        OracleDataAdapter adapter = new OracleDataAdapter(command);
                        dataTable = new DataTable();
                        adapter.Fill(dataTable);
                    }
                    return dataTable;
                }
            }
            catch (Exception)
            {
                return null;
            }
            return dataTable;
        }

        public override DataTable GetDatabaseAllViewNames()
        {
            DataTable dataTable = null;
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = "select v.view_name as ViewName from user_views v";
                        OracleDataAdapter adapter = new OracleDataAdapter(command);
                        dataTable = new DataTable();
                        adapter.Fill(dataTable);
                    }
                    return dataTable;
                }
            }
            catch (Exception)
            {
                return null;
            }
            return dataTable;
        }

        public override List<FieldInfo> GetTableAllFieldInfos(string tableName)
        {
            List<FieldInfo> list = new List<FieldInfo>();
            string tablePrimaryKeyName = this.GetTablePrimaryKeyName(tableName);
            List<FieldCommentInfo> tableFieldComments = this.GetTableFieldComments(tableName);
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = new OracleCommand("select c.column_name, c.data_type, c.nullable, c.data_length, c.data_precision, c.data_scale from user_tab_columns c where c.table_name = upper('" + tableName + "') order by c.column_id", connection))
                    {
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        Func<FieldCommentInfo, bool> func = null;
                        using (OracleDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                FieldInfo item = new FieldInfo {
                                    FieldName = dr["COLUMN_NAME"].ToString(),
                                    FieldType = dr["DATA_TYPE"].ToString(),
                                    IsNull = (dr["NULLABLE"].ToString() == "N") ? false : true,
                                    IsPkey = (dr["COLUMN_NAME"].ToString().ToLower() == tablePrimaryKeyName.ToLower()) ? true : false,
                                    TypeLength = dr["DATA_LENGTH"].ToString()
                                };
                                if (func == null)
                                {
                                    func = c => c.FieldName.ToLower() == dr["COLUMN_NAME"].ToString().ToLower();
                                }
                                List<FieldCommentInfo> list3 = Enumerable.Where<FieldCommentInfo>(tableFieldComments, func).ToList<FieldCommentInfo>();
                                item.Comment = ((list3 != null) && (list3.Count > 0)) ? list3[0].Comment.Replace("\r", "").Replace("\n", "") : "";
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        public override string GetTableCommentInfo(string tableName)
        {
            string str = "";
            OracleConnection connection = new OracleConnection(base.ConnectionString);
            try
            {
                connection.Open();
                using (OracleCommand command = new OracleCommand("select c.comments from user_tab_comments c where c.table_name = upper('" + tableName + "')", connection))
                {
                    object obj2 = command.ExecuteScalar();
                    if (obj2 != null)
                    {
                        str = obj2.ToString();
                    }
                }
                return str;
            }
            catch
            {
                str = "";
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            return str;
        }

        private List<FieldCommentInfo> GetTableFieldComments(string tableName)
        {
            string commandText = "select c.column_name,c.comments from user_col_comments c where c.table_name = upper('" + tableName + "') order by c.column_name";
            List<FieldCommentInfo> list = new List<FieldCommentInfo>();
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = new OracleCommand(commandText, connection))
                    {
                        connection.Open();
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                FieldCommentInfo item = new FieldCommentInfo {
                                    FieldName = reader["column_name"].ToString(),
                                    Comment = reader["comments"].ToString()
                                };
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        private string GetTablePrimaryKeyName(string tableName)
        {
            string str = "";
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = new OracleCommand("select a.COLUMN_NAME from user_cons_columns a inner join user_constraints b on a.CONSTRAINT_NAME=b.constraint_name where b.constraint_type = 'P' and b.table_name = upper('" + tableName + "')", connection))
                    {
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        object obj2 = command.ExecuteOracleScalar().ToString();
                        if (obj2 != null)
                        {
                            str = obj2.ToString();
                        }
                    }
                    return str;
                }
            }
            catch
            {
                return "";
            }
            return str;
        }

        public override List<FieldInfo> GetViewAllFieldInfos(string viewName)
        {
            List<FieldInfo> list = new List<FieldInfo>();
            try
            {
                using (OracleConnection connection = new OracleConnection(base.ConnectionString))
                {
                    using (OracleCommand command = new OracleCommand("select c.column_name, c.data_type, c.nullable, c.data_length, c.data_precision, c.data_scale from user_tab_cols c where c.table_name = upper('" + viewName + "') order by c.column_id", connection))
                    {
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                FieldInfo item = new FieldInfo {
                                    FieldName = reader["COLUMN_NAME"].ToString(),
                                    FieldType = reader["DATA_TYPE"].ToString(),
                                    IsNull = (reader["NULLABLE"].ToString() == "N") ? false : true,
                                    IsPkey = false,
                                    TypeLength = reader["DATA_LENGTH"].ToString(),
                                    Comment = ""
                                };
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        public override void SetConnectionString(string dbAdd, string dbName, string userName, string pwd)
        {
            string str = "Data Source=" + dbName + ";User Id=" + userName + ";Password=" + pwd + ";";
            base.ConnectionString = str;
        }
    }
}

